package mytest.sql;

public interface SqlConstants {
    String INSERT_PERFORMANCE_REPORT_SQL_1 = "select dial_task_gid,user_gid \n" +
            "  from log_dial_task_assign_%s \n" +
            "  where create_time >= %s and create_time <= %s";

    String INSERT_PERFORMANCE_REPORT_SQL_2 = "select task_id,user_classify,business_id,cus_cat,user_id,dial_status,loan_num,loan_amount \n" +
            "    from t_dial_task_%s";

    String INSERT_PERFORMANCE_REPORT_SQL_3 = "select task_id,user_id,call_length\n" +
            "  from t_call_log_%s \n" +
            "  where start_time >= %s and start_time <= %s + %s*24*60*60*1000";

    String PERFORMANCE_REPORT_SQL_1 = "INSERT INTO t_user_task_performance_report (\n" +
            "    cycle, division_day, user_id, user_name, user_classify, cus_cat, business_id, task_count,\n" +
            "    task_call_count, task_connect_count, call_count, connect_count,\n" +
            "    reach_count, loan_num, loan_amount, valid_flag\n" +
            ")\n" +
            "select \n" +
            " %s as cycle,\n" +
            " s.division_date as division_day,\n" +
            " s.user_gid as user_id,\n" +
            " s.username as user_name,\n" +
            " s.user_classify,\n" +
            " s.cus_cat,\n" +
            " s.business_id,\n" +
            " count(distinct s.dial_task_gid) as task_count,\n" +
            " count(DISTINCT CASE WHEN s.call_length IS NOT NULL THEN s.dial_task_gid END) AS task_call_count,\n" +
            " count(DISTINCT CASE WHEN s.call_length > 0 THEN s.dial_task_gid END) AS task_connect_count,\n" +
            " count(s.call_length) as call_count,\n" +
            " count(CASE WHEN s.call_length > 0 THEN 1 END) as connect_count,\n" +
            " sum(CASE WHEN s.dial_status = 3 THEN 1 ELSE 0 END) as reach_count,\n" +
            " sum(CASE WHEN s.dial_status = 3 THEN s.loan_num ELSE 0 END) as loan_num,\n" +
            " sum(CASE WHEN s.dial_status = 3 THEN s.loan_amount ELSE 0 END) as loan_amount,\n" +
            " 1\n" +
            "from \n" +
            "(select\n" +
            "  a.dial_task_gid, \n" +
            "  a.user_gid, \n" +
            "  b.username, \n" +
            "  '%s' as division_date, \n" +
            "  t.user_classify,\n" +
            "  t.business_id,\n" +
            "  t.cus_cat, \n" +
            "  t.user_id,\n" +
            "  t.dial_status,\n" +
            "  t.loan_num,\n" +
            "  t.loan_amount,\n" +
            "  c.call_length\n" +
            "from (";

    String PERFORMANCE_REPORT_SQL_2 = ") a \n" +
            "inner join\n" +
            "( ";

    String PERFORMANCE_REPORT_SQL_3 = ") t on a.dial_task_gid = t.task_id\n" +
            "left join t_user b on a.user_gid = b.user_id\n" +
            "left join (";

    String PERFORMANCE_REPORT_SQL_4 = ") c on a.dial_task_gid = c.task_id and a.user_gid=c.user_id\n" +
            ") s\n" +
            "GROUP BY s.division_date,s.user_gid,s.username,s.user_classify,s.business_id,s.cus_cat;";

    String INSERT_CALL_REPORT_SQL_1 = "select dial_task_gid,user_gid \n" +
            "  from log_dial_task_assign_%s \n" +
            "  where (end_time is null and create_time <= %s ) or (create_time <= %s  and end_time >= %s) ";

    String INSERT_CALL_REPORT_SQL_2 = "select task_id,user_classify,business_id,cus_cat from t_dial_task_%s";

    String INSERT_CALL_REPORT_SQL_3 = "select task_id,user_id,call_length\n" +
            "    from t_call_log_%s \n" +
            "  where start_time >= %s and start_time <= %s";

    String CALL_REPORT_SQL_1 ="INSERT INTO t_user_task_call_report (\n" +
            "    work_day, user_id, user_name, user_classify, cus_cat, business_id, task_count,\n" +
            "    task_call_count, task_connect_count, call_count, connect_count,\n" +
            "    connect_total_time, valid_flag\n" +
            ")\n" +
            "select \n" +
            " s.work_date as work_day, \n" +
            " s.user_gid as user_id,\n" +
            " s.username as user_name,\n" +
            " s.user_classify,\n" +
            " s.cus_cat,\n" +
            " s.business_id,\n" +
            " count(distinct s.dial_task_gid) as task_count,\n" +
            " count(DISTINCT CASE WHEN s.call_length IS NOT NULL THEN s.dial_task_gid END) AS task_call_count,\n" +
            " count(DISTINCT CASE WHEN s.call_length > 0 THEN s.dial_task_gid END) AS task_connect_count,\n" +
            " count(s.call_length) as call_count,\n" +
            " count(CASE WHEN s.call_length > 0 THEN 1 END) as connect_count,\n" +
            " IFNULL(SUM(s.call_length), 0) as connect_total_time,\n" +
            " 1\n" +
            "from \n" +
            "(select\n" +
            "  a.dial_task_gid, \n" +
            "  a.user_gid, \n" +
            "  b.username, \n" +
            "  '%s' as work_date, \n" +
            "  t.user_classify,\n" +
            "  t.business_id,\n" +
            "  t.cus_cat,\n" +
            "  c.call_length\n" +
            "from (";

    String CALL_REPORT_SQL_2 = ") a \n" +
            "inner join\n" +
            "(";
    String CALL_REPORT_SQL_3 = ") t on a.dial_task_gid = t.task_id\n" +
            "left join t_user b on a.user_gid = b.user_id\n" +
            "left join (";
    String CALL_REPORT_SQL_4 = ") c on a.dial_task_gid = c.task_id and a.user_gid=c.user_id\n" +
            ") s\n" +
            "GROUP BY s.work_date,s.user_gid,s.username,s.user_classify,s.business_id,s.cus_cat\n";

}
